﻿using HsServerHa.Core.Http;
using HsServerHa.Entity.Models;
using HsServerHa.Entity.Models.SqlserverMirror;
using HsServerHa.Utility.FileHelper;
using HsServerHa.Utility.HttpHelper;
using HsServerHa.Utility.SQLite;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Configuration;
using System.IO;
using System.Linq;
using System.Net;
using System.Net.NetworkInformation;
using System.Net.Sockets;
using System.Runtime.CompilerServices;
using System.Text;
using System.Threading.Tasks;

namespace HsServerHa.Utility.SqlserverMirror
{
    public static class CheckStatementCommand
    {
        static ObservableCollection<CheckStatementsEntity> checkStatementsEntities;


        public static string GetCheckStatementsConfig()
        {
            var a = File.ReadAllText("Config\\CheckStatements.json");
            return a;
        }

        /// <summary>
        /// 操作语句
        /// </summary>
        public static ObservableCollection<CheckStatementsEntity> CheckStatementsEntities
        {
            get
            {
                if (checkStatementsEntities == null)
                {
                    var json = GetCheckStatementsConfig();
                    checkStatementsEntities = JsonConvert.DeserializeObject<ObservableCollection<CheckStatementsEntity>>(json);
                    //checkStatementsEntities = new ObservableCollection<CheckStatementsEntity>(SQLiteHelper.Table<CheckStatementsEntity>().ToList());
                    //string s =JsonConvert.SerializeObject(checkStatementsEntities);


                }
                return checkStatementsEntities;
            }
        }

        /// <summary>
        /// 数据库检查
        /// </summary>
        /// <param name="DbConnectString"></param>
        /// <returns></returns>
        public static ObservableCollection<CheckStatementsEntity> DBCheck(string DbConnectString)
        {
            ObservableCollection<CheckStatementsEntity> checkStatements = new ObservableCollection<CheckStatementsEntity>();

            var dbCheck = CheckStatementsEntities.Where(s => s.type == "check");
            DbHelperSQL db = new DbHelperSQL(DbConnectString);
            foreach (var dbCheckEntity in dbCheck)
            {
                var DataBases = db.Query(dbCheckEntity.Statement);
                if (DataBases == null) throw new Exception("数据库操作失败，请检查数据库配置！");
                if (DataBases.Tables[0].Rows.Count == 0)
                    dbCheckEntity.Result = "false";
                else
                    dbCheckEntity.Result = "true";
                checkStatements.Add(dbCheckEntity.Copy());
            }

            return checkStatements;
        }

        /// <summary>
        /// 初始化数据库镜像
        /// </summary>
        /// <param name="DbConnectString"></param>
        /// <returns></returns>
        public static ObservableCollection<CheckStatementsEntity> InitDatabaseMirror(ServiceInfoEntity serviceInfo, string path = "D:\\dbback")
        {

            ObservableCollection<CheckStatementsEntity> checkStatements = new ObservableCollection<CheckStatementsEntity>();
            var dbCheck = CheckStatementsEntities.Where(s => s.type == "drop");

            foreach (var item in dbCheck)
            {
                var newItem = item.Copy();
                if (newItem.type == "Generate")
                    newItem.Statement = item.Statement.Replace("d:\\dbback", path).Replace("HOST_1", serviceInfo.HostName);
                checkStatements.Add(newItem);
            }
            DbHelperSQL db = new DbHelperSQL(serviceInfo.DBConnectStr);
            foreach (var dbCheckEntity in checkStatements)
            {
                try
                {
                    string err = "";
                    var DataBases = db.ExecuteSql(dbCheckEntity.Statement, 10000, out err);
                    dbCheckEntity.Result = "true";
                    if (!string.IsNullOrEmpty(err))
                    {
                        dbCheckEntity.Result = err;
                    }
                }
                catch (Exception ex)
                {
                    dbCheckEntity.Result = "false";
                }
            }
            return checkStatements;
        }


        /// <summary>
        /// 生成证书，配置出站
        /// </summary>
        /// <param name="DbConnectString"></param>
        /// <returns></returns>
        public static ObservableCollection<CheckStatementsEntity> ResetMirror(ServiceInfoEntity serviceInfo, string path = "d:\\dbback")
        {

            ObservableCollection<CheckStatementsEntity> checkStatements = new ObservableCollection<CheckStatementsEntity>();
            var dbCheck = CheckStatementsEntities.Where(s => s.type == "drop" || s.type == "Generate");

            foreach (var item in dbCheck)
            {
                var newItem = item.Copy();
                if (newItem.type == "Generate")
                    newItem.Statement = item.Statement.Replace("d:\\dbback", path).Replace("HOST_1", serviceInfo.HostName);
                checkStatements.Add(newItem);
                //item.ToCopy<CheckStatementsEntity>();
            }
            DbHelperSQL db = new DbHelperSQL(serviceInfo.DBConnectStr);
            foreach (var dbCheckEntity in checkStatements)
            {
                try
                {
                    string err = "";
                    var DataBases = db.ExecuteSql(dbCheckEntity.Statement, 10000, out err);
                    dbCheckEntity.Result = "true";
                    if (!string.IsNullOrEmpty(err))
                    {
                        dbCheckEntity.Result = err;
                    }
                }
                catch (Exception ex)
                {
                    dbCheckEntity.Result = "false";
                }
            }




            return checkStatements;
        }

        /// <summary>
        /// 配置入站
        /// </summary>
        /// <param name="DbConnectString"></param>
        /// <returns></returns>
        public static ObservableCollection<CheckStatementsEntity> ImportCertificate(ServiceInfoEntity serviceInfo, string path = "d:\\dbback")
        {
            ObservableCollection<CheckStatementsEntity> checkStatements = new ObservableCollection<CheckStatementsEntity>();
            var dbCheck = CheckStatementsEntities.Where(s => s.type == "ImportCertificate");
            DbHelperSQL db = new DbHelperSQL(serviceInfo.DBConnectStr);

            foreach (var item in dbCheck)
            {
                var newItem = item.Copy();
                if (serviceInfo.HostName == "HOST_2")
                    newItem.Statement = item.Statement.Replace("d:\\dbback", path).Replace("HOST_2_cert", "HOST_1_cert");
                else
                    newItem.Statement = item.Statement.Replace("d:\\dbback", path).Replace("HOST_1_cert", "HOST_2_cert");
                checkStatements.Add(newItem);
            }
            foreach (var dbCheckEntity in checkStatements)
            {
                string err = "";
                var DataBases = db.ExecuteSql(dbCheckEntity.Statement, 10000, out err);

                if (!string.IsNullOrEmpty(err))
                {
                    dbCheckEntity.Result = err;
                }
                else
                {
                    dbCheckEntity.Result = "true";
                }

            }





            return checkStatements;
        }

        /// <summary>
        /// 集合拼接
        /// </summary>
        /// <param name="checkStatementsEntities"></param>
        /// <param name="ADDStatementsEntities"></param>
        public static void Add(this ObservableCollection<CheckStatementsEntity> checkStatementsEntities, ObservableCollection<CheckStatementsEntity> ADDStatementsEntities)
        {
            foreach (var item in ADDStatementsEntities)
            {
                checkStatementsEntities.Add(item.Copy());
            }
        }

        /// <summary>
        /// 检查结果是否通过
        /// </summary>
        /// <param name="checkStatementsEntities"></param>
        /// <returns></returns>
        public static bool DBCheckResult(this ObservableCollection<CheckStatementsEntity> checkStatementsEntities)
        {
            foreach (var item in checkStatementsEntities)
            {
                if (item.Result != "true" && item.IsCheck == "Check")
                    return false;
            }
            return true;
        }

        /// <summary>
        /// 备份主体
        /// </summary>
        /// <param name="DbConnectString"></param>
        /// <returns></returns>
        public static bool BackMainDb(ObservableCollection<string> log, string dbConnStr, string dataBaseName, string path, string slaveServer, bool isAutoCopy)
        {
            string err = "";
            var statements = CheckStatementCommand.CheckStatementsEntities.Where(s => s.type == "backups");
            AddMirrorCommand.Add($"*开始备份主体,数据库名称：{dataBaseName}");
            foreach (var statement in statements)
            {
                var item = statement.Copy();
                item.Statement = statement.Statement.Replace("DataBaseName", dataBaseName).Replace("d:\\dbback", path);
                DbHelperSQL db = new DbHelperSQL(dbConnStr);
                AddMirrorCommand.Add(statement.CheckItem);
                var DataBases = db.ExecuteSql(item.Statement, 10000, out err);
                if (!string.IsNullOrEmpty(err))
                {

                    AddMirrorCommand.Add(err);
                    return false;
                }
            }

            //var dbBackFilePathEntity = SQLiteHelper.Table<DbBackFilePathEntity>().ToList().FirstOrDefault();
            //if (dbBackFilePathEntity != null)
            //{
            //    if (dbBackFilePathEntity.IsSyncBackupFile == "true")
            //    {

            //    }
            //}

            //复制数据库备份文件到从机服务器上
            if (isAutoCopy)
            {
                var getMasterSlaveBindEntity = SQLiteHelper.Table<MasterSlaveBindEntity>().FirstOrDefault();
                if (getMasterSlaveBindEntity != null)
                    CopyDbBackFileToSlaveServer(getMasterSlaveBindEntity.SlaveIP, dataBaseName);
                else
                    LoggerHelper.Error("主从绑定关系获取失败。无法执行数据库备份传输操作！");
            }


            return true;
        }

        /// <summary>
        /// 同步证书到从机
        /// </summary>
        /// <param name="slaveServer"></param>
        /// <param name="dbBackFolder"></param>
        public static void SyncCertificateToSlaveServer(string slaveServer, string dbBackFolder)
        {
            //主机证书名称
            string hostCerName = "HOST_1_cert.cer";
            //获取端口号
            int port = GetRandGeneratePort();
            var file = new TransFileEntity() { FilePath = $"{dbBackFolder}\\{hostCerName}", Port = port };
            SyncFileToRemoteServer(file, slaveServer);
        }

        /// <summary>
        /// 获取随机生成端口号
        /// </summary>
        /// <returns></returns>
        public static int GetRandGeneratePort()
        {
            Random rand = new Random();
            int port = rand.Next(1024, 49151);
            bool isExecute = true;
            while (isExecute)
            {
                //端口被占用，重新随机生成
                if (IsPortInUse(port))
                {
                    port = rand.Next(1024, 49151);
                }
                else
                {
                    isExecute = false;
                }
            }
            return port;
        }

        /// <summary>
        /// 判断端口是否被占用
        /// </summary>
        /// <param name="port"></param>
        /// <returns></returns>
        public static bool IsPortInUse(int port)
        {
            IPGlobalProperties properties = IPGlobalProperties.GetIPGlobalProperties();
            IPEndPoint[] endPoints = properties.GetActiveTcpListeners();

            foreach (IPEndPoint endPoint in endPoints)
            {
                if (endPoint.Port == port)
                {
                    return true;
                }
            }

            return false;
        }

        /// <summary>
        /// 同步证书到主机
        /// </summary>
        /// <param name="hostSerever"></param>
        /// <param name="slaveServer"></param>
        /// <param name="dbBackFolder"></param>
        public static async void SyncCertificateToHostServer(string hostSerever, string slaveServer, string dbBackFolder)
        {
            //从机证书名称
            string slaveCerName = "HOST_2_cert.cer";
            string targetFilePath = $"{dbBackFolder}\\{slaveCerName}";
            Random rand = new Random();
            int port = GetRandGeneratePort();
            //证书同步监听
            CertificateSyncListener(port, targetFilePath);

            //调用远程接口发送证书同步文件
            var transFile = new TransFileEntity()
            {
                ServerIp = hostSerever,
                Port = port,
                FilePath = targetFilePath,
            };

            var slavePath = $"http://{slaveServer}:{ConfigurationManager.AppSettings["HttpPort"]?.ToString() ?? "6601"}/";
            var tcpListenerResult = HttpHelpers.PostToJson<HttpResponseResultModel<object>>(slavePath +
           (ConfigurationManager.AppSettings["sendCertificateFile"]?.ToString() ?? "sendCertificateFile"), JsonConvert.SerializeObject(transFile));

        }

        /// <summary>
        /// 证书同步监听
        /// </summary>
        /// <param name="port"></param>
        /// <param name="targetFilePath"></param>
        private static async Task CertificateSyncListener(int port, string targetFilePath)
        {
            try
            {
                Task.Run(() =>
                {
                    TcpListener FileListener = new TcpListener(IPAddress.Any, port);
                    FileListener.Start();
                    LoggerHelper.Info("监听已启动,等待连接...", "证书备份文件同步监听");
                    bool isCanExecute = true;
                    while (isCanExecute)
                    {
                        TcpClient fileClient = FileListener.AcceptTcpClient();
                        NetworkStream fileNetworkStream = fileClient.GetStream();

                        byte[] buffer = new byte[8096];
                        int bytesRead;

                        using (FileStream fileStream = File.OpenWrite(targetFilePath)) // 设置目标文件路径
                        {
                            while ((bytesRead = fileNetworkStream.Read(buffer, 0, buffer.Length)) > 0)
                            {
                                fileStream.Write(buffer, 0, bytesRead);
                            }
                        }
                        fileNetworkStream.Close();
                        fileClient.Close();
                        LoggerHelper.Info("文件保存完成", "数据库备份文件复制监听");
                        isCanExecute = false;
                    }
                });

            }
            catch (IOException e)
            {
                LoggerHelper.Error($"{e.Message}", "数据库备份文件复制监听");
            }
        }


        #region 从主机复制数据库备份文件到从机
        private static void CopyDbBackFileToSlaveServer(string slaveServer, string dataBaseName)
        {
            //获取db备份文件保存路径
            var dbBackFilePathEntity = SQLiteHelper.Table<DbBackFilePathEntity>().ToList().FirstOrDefault();
            string dbBackFolder = @"d:\\dbback";
            if (dbBackFilePathEntity != null)
            {
                dbBackFolder = dbBackFilePathEntity.FilePath;
            }

            //定义数据库备份文件和数据库日志备份文件
            string dbBackFile = $"{dataBaseName}.bak";
            string dbLogBackFile = $"{dataBaseName}_log.bak";

            var transFileList = new List<TransFileEntity>();
            int port1 = GetRandGeneratePort();
            int port2 = GetRandGeneratePort();
            transFileList.Add(new TransFileEntity() { FilePath = $"{dbBackFolder}\\{dbBackFile}", Port = port1 });
            transFileList.Add(new TransFileEntity() { FilePath = $"{dbBackFolder}\\{dbLogBackFile}", Port = port2 });

            foreach (var file in transFileList)
            {
                //同步文件到远程服务器
                SyncFileToRemoteServer(file, slaveServer);

            }
        }




        /// <summary>
        /// 同步文件至远程服务器
        /// </summary>
        /// <param name="file"></param>
        /// <param name="slaveServer"></param>
        private static void SyncFileToRemoteServer(TransFileEntity file, string slaveServer)
        {
            //设置tcpListener监听
            var slavePath = $"http://{slaveServer}:{ConfigurationManager.AppSettings["HttpPort"]?.ToString() ?? "6601"}/";
            var tcpListenerResult = HttpHelpers.PostToJson<HttpResponseResultModel<object>>(slavePath +
           (ConfigurationManager.AppSettings["setTcpListener"]?.ToString() ?? "setTcpListener"), JsonConvert.SerializeObject(file));
            //System.Threading.Thread.Sleep(500);
            int failedCount = 0;
            bool isCanExecute = true;
            //执行失败一直执行
            while (isCanExecute && failedCount < 10)
            {
                var result = FileTransferClient.TransferFile(slaveServer, file.Port, file.FilePath);
                if (result)
                {
                    isCanExecute = false;
                    LoggerHelper.Info($"{file.FilePath}同步完成");
                }
                else
                {
                    //未能连接远程服务器重新调用接口启动远程监听
                    LoggerHelper.Error($"{file.FilePath}未能连接远程服务器重新调用接口启动远程监听,端口：{file.Port}");
                    var retryTcpListenerResult = HttpHelpers.PostToJson<HttpResponseResultModel<object>>(slavePath +
                   (ConfigurationManager.AppSettings["setTcpListener"]?.ToString() ?? "setTcpListener"), JsonConvert.SerializeObject(file));
                    System.Threading.Thread.Sleep(3000);
                    failedCount++;
                }

            }
        }

        #endregion


        /// <summary>
        /// 还原镜像
        /// </summary>
        /// <param name="log"></param>
        /// <param name="dbConnStr"></param>
        /// <returns></returns>
        public static bool Restore(ObservableCollection<string> log, string dbConnStr, string dataBaseName, string path)
        {
            string err = "";
            var statements = CheckStatementCommand.CheckStatementsEntities.Where(s => s.type == "restore");
            AddMirrorCommand.Add($"*开始还原数据库镜像,数据库名称：{dataBaseName}");
            foreach (var statement in statements)
            {
                var item = statement.Copy();
                item.Statement = statement.Statement.Replace("DataBaseName", dataBaseName).Replace("d:\\dbback", path);
                DbHelperSQL db = new DbHelperSQL(dbConnStr);
                AddMirrorCommand.Add(item.CheckItem);
                var DataBases = db.ExecuteSql(item.Statement, 10000, out err);
                if (!string.IsNullOrEmpty(err))
                {

                    AddMirrorCommand.Add(err);

                    return false;
                }
            }

            return true;
        }

        /// <summary>
        /// 设置伙伴关系
        /// </summary>
        /// <param name="log"></param>
        /// <param name="dbConnStr"></param>
        /// <param name="dataBaseName"></param>
        /// <param name="ServerName"></param>
        /// <returns></returns>
        public static bool SetPartner(ObservableCollection<string> log, string dbConnStr, string dataBaseName, string ServerName)
        {
            string err = "";
            var statements = CheckStatementCommand.CheckStatementsEntities.Where(s => s.type == "setpartner");
            AddMirrorCommand.Add(@$"*开始设置服务器({ServerName})镜像关系,数据库名称：{dataBaseName}");
            foreach (var statement in statements)
            {
                var item = statement.Copy();
                item.Statement = statement.Statement.Replace("DataBaseName", dataBaseName).Replace("IPAddress", ServerName);
                DbHelperSQL db = new DbHelperSQL(dbConnStr);
                AddMirrorCommand.Add(item.CheckItem);
                var DataBases = db.ExecuteSql(item.Statement, 10000, out err);
                if (!string.IsNullOrEmpty(err))
                {
                    AddMirrorCommand.Add(err);
                    return false;
                }
            }

            return true;
        }


        /// <summary>
        /// 设置伙伴关系
        /// </summary>
        /// <param name="log"></param>
        /// <param name="dbConnStr"></param>
        /// <param name="dataBaseName"></param>
        /// <param name="ServerName"></param>
        /// <returns></returns>
        public static bool DeletePartner(ObservableCollection<string> log, string dbConnStr, string dataBaseName, string ServerName)
        {
            string err = "";
            var statements = CheckStatementCommand.CheckStatementsEntities.Where(s => s.type == "deletepartner");
            AddMirrorCommand.Add(@$"*开始服务器({ServerName})断开镜像关系,数据库名称：{dataBaseName}");
            foreach (var statement in statements)
            {
                var item = statement.Copy();
                item.Statement = statement.Statement.Replace("DataBaseName", dataBaseName).Replace("IPAddress", ServerName);
                DbHelperSQL db = new DbHelperSQL(dbConnStr);
                AddMirrorCommand.Add(item.CheckItem);
                var DataBases = db.ExecuteSql(item.Statement, 10000, out err);
                if (!string.IsNullOrEmpty(err))
                {
                    if (err.Contains("没有为数据库镜像配置数据库"))
                    {
                        return true;
                    }
                    AddMirrorCommand.Add(err);
                    return false;
                }
            }

            return true;
        }

        /// <summary>
        /// 设置伙伴关系
        /// </summary>
        /// <param name="log"></param>
        /// <param name="dbConnStr"></param>
        /// <param name="dataBaseName"></param>
        /// <param name="ServerName"></param>
        /// <returns></returns>
        public static bool DeleteDataBase(ObservableCollection<string> log, string dbConnStr, string dataBaseName, string ServerName)
        {
            string err = "";

            AddMirrorCommand.Add(@$"*开始服务器({ServerName})删除数据,数据库名称：{dataBaseName}");

            var statement = "DROP DATABASE [DataBaseName]";
            statement = statement.Replace("DataBaseName", dataBaseName);
            DbHelperSQL db = new DbHelperSQL(dbConnStr);
            var DataBases = db.ExecuteSql(statement, 10000, out err);
            if (!string.IsNullOrEmpty(err))
            {
                AddMirrorCommand.Add(err);
                return false;
            }
            return true;
        }
    }
}
